/*函数*/
--返回指定的数据表。
if exists(select 0 from sysobjects where xtype = 'tf' and name = 'fnESGetOffSprings')
begin
    print 'drop function fnESGetOffSprings'
    drop function fnESGetOffSprings
end
go
    print 'create function fnESGetOffSprings'
go
create function fnESGetOffSprings
(
    @TableName      nvarchar(128),--数据表名称。
    @FieldValue     nvarchar(64),--项目值。
    @IncludeSelf    bit = 0,--是否包含自己。
    @Seperator      nvarchar(10) = '>'--名称连接字符。
)
returns @tableResult table(
    FieldID         nvarchar(32) primary key,
    FieldName   nvarchar(256),
    FullName     nvarchar(2048),
    LevelNum    int
)
as
begin
    declare @v_table table(
                            FieldID     nvarchar(32) primary key,
                            FieldName   nvarchar(256),
                            FullName    nvarchar(1024) default null,
                            LevelNum    int,
                            [No]        int    identity(1,1)          
                        )
    declare @v_Level int
    set @v_Level = 0
    --tblESCatalog
    if(@TableName = 'tblESCatalog')
    begin
        --插入自己（或所有同等级的）数据。
        insert into @v_table(FieldID,FieldName,FullName,LevelNum)
        select CatalogID,CatalogName,CatalogName,@v_Level
        from tblESCatalog
        where (CatalogID = @FieldValue) or (isnull(@FieldValue,'') = '' and isnull(ParentCatalogID,'') = '')
        --循环插入自己的子孙。
        while(@@rowcount > 0)
        begin
            set @v_Level = @v_Level + 1
            
            insert into @v_table(FieldID,FieldName,FullName,LevelNum)
            select data.CatalogID,data.CatalogName,tmp.FullName + @Seperator + data.CatalogName,@v_Level
            from tblESCatalog data
            inner join @v_table tmp
            on tmp.FieldID = data.ParentCatalogID
            where not exists(select 0 
                            from @v_table tmp2
                            where tmp2.FieldID = data.CatalogID)
        end
    end
    --tblESTeachingSections
    if(@TableName = 'tblESTeachingSections')
    begin
        --插入自己（或所有同等级的）数据。
        insert into @v_table(FieldID,FieldName,FullName,LevelNum)
        select  SectionID,SectionName,SectionName,@v_Level
        from  tblESTeachingSections
        where (SectionID = @FieldValue) or (isnull(@FieldValue,'') = '' and isnull(ParentSectionID,'') = '')
        --循环插入自己的子孙。
        while(@@rowcount > 0)
        begin
            set @v_Level = @v_Level + 1
            
            insert into @v_table(FieldID,FieldName,FullName,LevelNum)
            select data.SectionID,data.SectionName,tmp.FullName + @Seperator + data.SectionName,@v_Level
            from tblESTeachingSections data
            inner join @v_table tmp
            on tmp.FieldID = data.ParentSectionID
            where not exists(select 0 
                            from @v_table tmp2
                            where tmp2.FieldID = data.SectionID)
        end
    end
    --剔除自己。
    if(@IncludeSelf = 0)
    begin
        delete from @v_table where (FieldID = @FieldValue) or (isnull(@FieldValue,'') = ''  and isnull(FieldID,'') = '')
    end
    --返回结果数据。
    insert into @tableResult(FieldID,FieldName,FullName,LevelNum)
    select FieldID,FieldName,FullName,LevelNum
    from @v_table
    order by LevelNum,[No]
    --返回
    return
end
go
---------------------------------------------------------------------------------------------------------------------------------
--根据FieldID获取全名称。
if exists(select 0 from sysobjects where xtype = 'fn' and name = 'fnESGetFullName')
begin
    print 'drop function fnESGetFullName'
    drop function fnESGetFullName
end
go
    print 'create function fnESGetFullName'
go
create function fnESGetFullName
(
    @TableName      nvarchar(128),--数据表名称。
    @FieldValue     nvarchar(64)--项目值。
)
returns nvarchar(1024)
begin
    declare @result nvarchar(1024)
    select top 1 @result = FullName 
    from dbo.fnESGetOffSprings(@TableName,null,0,'>')
    where FieldID = @FieldValue
    return(@result)
end
go
---------------------------------------------------------------------------------------------------------------------------------